package ym.ecrm.core.dao.systemManagement.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import ym.ecrm.core.common.util.Pager;
import ym.ecrm.core.dao.base.impl.HibernateEntityDAO;
import ym.ecrm.core.dao.systemManagement.IRoleDAO;
import ym.ecrm.core.domain.systemManagement.Role;
import ym.ecrm.core.dto.systemManagement.RoleQueryConditionDTO;

@Repository
@SuppressWarnings("unchecked")
public class HibernateRoleDAO extends HibernateEntityDAO<Role> implements IRoleDAO{

	//@Override
	public Pager<Role> findRoles(final int pageNumber, final int pageSize) {
		
		Long totalCount = getHibernateTemplate().execute(new HibernateCallback() {
			//@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List list = session.createQuery("select count(*) from Role").list();
				if(list != null && !list.isEmpty())
					return list.get(0);
				return null;
			}
		});
		
		if(totalCount == null || totalCount < 1){
			return new Pager<Role>(pageNumber, pageSize, 0l, null);
		}
		
		List<Role> list = getHibernateTemplate().executeFind(
				new HibernateCallback<List<Role>>() {

					//@Override
					public List<Role> doInHibernate(Session session)
							throws HibernateException, SQLException {
						int firstIndex = Pager.getStartIndex(pageNumber, pageSize);
						return session.createQuery("from Role")
							.setFirstResult(firstIndex)
							.setMaxResults(pageSize).list();
					}
				});
		
		
		return new Pager<Role>(pageNumber,pageSize,totalCount,list);
	}

	//@Override
	public List<Role> findRoles(RoleQueryConditionDTO queryConditionDTO) {
		return findRoles(queryConditionDTO, 0, 0).getData();
	}

	//@Override
	public Pager<Role> findRoles(final RoleQueryConditionDTO queryConditionDTO,
			final int pageNumber, final int pageSize) {
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append("select count(*) from Role role");
		countBuffer.append(" where (:roleCode is null or lower(role.roleCode) like :roleCode)");
		countBuffer.append(" and (:roleName is null or lower(role.roleName) like :roleName)");
		countBuffer.append(" and (:builtIn is null or lower(role.builtIn) = :builtIn)");
		countBuffer.append(" order by role.id asc");
		final String countString = countBuffer.toString();
		
		StringBuffer hqlBuffer = new StringBuffer();
		hqlBuffer.append("from Role role");
		hqlBuffer.append(" where (:roleCode is null or lower(role.roleCode) like :roleCode)");
		hqlBuffer.append(" and (:roleName is null or lower(role.roleName) like :roleName)");
		hqlBuffer.append(" and (:builtIn is null or lower(role.builtIn) = :builtIn)");
		hqlBuffer.append(" order by role.id asc");
		final String hqlString = hqlBuffer.toString();
		
		Long totalCount = getHibernateTemplate().execute(new HibernateCallback() {

			//@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(countString);
				query.setParameter("roleCode",
						queryConditionDTO.getRoleCode() == null? null: "%"+queryConditionDTO.getRoleCode()+"%");
				query.setParameter("roleName",
						queryConditionDTO.getRoleName() == null? null: "%"+queryConditionDTO.getRoleName()+"%");
				query.setParameter("builtIn",
						queryConditionDTO.getBuiltIn() == null? null:queryConditionDTO.getBuiltIn());
				return query.uniqueResult();
			}
			
		});
		
		if(totalCount == null || totalCount < 1){
			return null;
		}
		
		List<Role> list = getHibernateTemplate().executeFind(
				new HibernateCallback<List<Role>>() {

			//@Override
			public List<Role> doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hqlString);
				query.setParameter("roleCode",
						queryConditionDTO.getRoleCode() == null? null: "%"+queryConditionDTO.getRoleCode()+"%");
				query.setParameter("roleName",
						queryConditionDTO.getRoleName() == null? null:"%"+queryConditionDTO.getRoleName()+"%");
				query.setParameter("builtIn",
						queryConditionDTO.getBuiltIn() == null? null:queryConditionDTO.getBuiltIn());
				if(pageSize == 0 && pageNumber == 0){
					return query.list();
				}else{
					int firstIndex = Pager.getStartIndex(pageNumber, pageSize);
					return query.setFirstResult(firstIndex)
						.setMaxResults(pageSize).list();
				}
			}
		});
		return new Pager<Role>(pageNumber, pageSize, totalCount, list);
	}

}
